package com.datamation.swadeshi.control.data;

import java.util.ArrayList;

import com.datamation.swadeshi.model.Area;
import com.datamation.swadeshi.model.InvDet;
import com.datamation.swadeshi.model.InvHed;
import com.datamation.swadeshi.model.OrdDet;
import com.datamation.swadeshi.model.OrdHed;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class InvDetDS {

	private SQLiteDatabase dB;
	private DatabaseHelper dbHelper;
	Context context;
	private String TAG="swadeshi";
	
	
	public InvDetDS (Context context){
		this.context = context;
		dbHelper = new DatabaseHelper(context);
	}
	
	public void open() throws SQLException {
		dB = dbHelper.getWritableDatabase();
	}
	
	@SuppressWarnings("static-access")
	public int createOrUpdateInvDet(ArrayList<InvDet>  list) {
		
		int count =0;
		
		if(dB == null){
			open();
		}else if(!dB.isOpen()){
			open();
		}
		Cursor cursor = null;
		
	try{
		
			
    for (InvDet invDet : list) {
    	
    		ContentValues values = new ContentValues();
			
			
			String selectQuery = "SELECT * FROM " + dbHelper.TABLE_FINVDET 
								+ " WHERE " + dbHelper.FINVDET_ID + " = '" +invDet.getFINVDET_ID() + "'";	
			
			cursor = dB.rawQuery(selectQuery, null);
    		
			values.put(dbHelper.FINVDET_AMT, invDet.getFINVDET_AMT());
			values.put(dbHelper.FINVDET_BAL_QTY, invDet.getFINVDET_BAL_QTY());
			values.put(dbHelper.FINVDET_B_AMT, invDet.getFINVDET_B_AMT());
			values.put(dbHelper.FINVDET_B_DIS_AMT, invDet.getFINVDET_B_DIS_AMT());
			values.put(dbHelper.FINVDET_BP_DIS_AMT, invDet.getFINVDET_BP_DIS_AMT());
			values.put(dbHelper.FINVDET_B_SELL_PRICE, invDet.getFINVDET_B_SELL_PRICE());
			values.put(dbHelper.FINVDET_BT_TAX_AMT, invDet.getFINVDET_BT_TAX_AMT());
			values.put(dbHelper.FINVDET_BT_SELL_PRICE, invDet.getFINVDET_BT_SELL_PRICE());
			values.put(dbHelper.FINVDET_CASE, invDet.getFINVDET_CASE());
			values.put(dbHelper.FINVDET_CASE_QTY, invDet.getFINVDET_CASE_QTY());
			values.put(dbHelper.FINVDET_DIS_AMT, invDet.getFINVDET_DIS_AMT());
			values.put(dbHelper.FINVDET_DIS_PER, invDet.getFINVDET_DIS_PER());
			values.put(dbHelper.FINVDET_FREE_QTY, invDet.getFINVDET_FREE_QTY());
			values.put(dbHelper.FINVDET_ITEM_CODE, invDet.getFINVDET_ITEM_CODE());
			values.put(dbHelper.FINVDET_P_DIS_AMT, invDet.getFINVDET_P_DIS_AMT());
			values.put(dbHelper.FINVDET_PRIL_CODE, invDet.getFINVDET_PRIL_CODE());
			values.put(dbHelper.FINVDET_QTY, invDet.getFINVDET_QTY());
			values.put(dbHelper.FINVDET_DIS_VAL_AMT, invDet.getFINVDET_DIS_VAL_AMT());
			values.put(dbHelper.FINVDET_PICE_QTY, invDet.getFINVDET_PICE_QTY());
			values.put(dbHelper.FINVDET_REA_CODE, invDet.getFINVDET_REA_CODE());
			values.put(dbHelper.FINVDET_TYPE, invDet.getFINVDET_TYPE());
			values.put(dbHelper.FINVDET_RECORD_ID, invDet.getFINVDET_RECORD_ID());
			values.put(dbHelper.FINVDET_REFNO, invDet.getFINVDET_REFNO());
			values.put(dbHelper.FINVDET_SELL_PRICE, invDet.getFINVDET_SELL_PRICE());
			values.put(dbHelper.FINVDET_SEQNO, invDet.getFINVDET_SEQNO());
			values.put(dbHelper.FINVDET_TAX_AMT, invDet.getFINVDET_TAX_AMT());
			values.put(dbHelper.FINVDET_TAX_COM_CODE, invDet.getFINVDET_TAX_COM_CODE());
			values.put(dbHelper.FINVDET_TIMESTAMP_COLUMN, invDet.getFINVDET_TIMESTAMP_COLUMN());
			values.put(dbHelper.FINVDET_T_SELL_PRICE, invDet.getFINVDET_T_SELL_PRICE());
			values.put(dbHelper.FINVDET_TXN_DATE, invDet.getFINVDET_TXN_DATE());
			values.put(dbHelper.FINVDET_TXN_TYPE, invDet.getFINVDET_TXN_TYPE());
			values.put(dbHelper.FINVDET_IS_ACTIVE, invDet.getFINVDET_IS_ACTIVE());
			
			

			int cn = cursor.getCount();
			if(cn>0){
				
				count = dB.update(dbHelper.TABLE_FINVDET, values ,dbHelper.FINVDET_ID + " =?",	
						new String[] { String.valueOf(invDet.getFINVDET_ID())});
				
			}else{
				count = (int) dB.insert(dbHelper.TABLE_FINVDET, null, values);
			}
			
			}
    
		}catch (Exception e) {
		
			Log.v(TAG+" Exception", e.toString());
	
		}finally {  
			if (cursor !=null) {
				cursor.close();
			}
			dB.close();
		}
		return count;
				
	}
	
	public ArrayList<InvDet> getAllInvDet(String refno) {
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		ArrayList<InvDet> list = new ArrayList<InvDet>();
		
		String selectQuery = "select * from "+dbHelper.TABLE_FINVDET +" WHERE "+dbHelper.FINVDET_TYPE+" in ('SA','FI') AND "+dbHelper.FINVDET_REFNO+"='"+refno+"'";
		
		Cursor cursor = dB.rawQuery(selectQuery, null);
		while(cursor.moveToNext()){
			
			InvDet invDet =new InvDet();
		
			invDet.setFINVDET_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_ID)));
			invDet.setFINVDET_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_AMT)));
			invDet.setFINVDET_BAL_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_BAL_QTY)));
			invDet.setFINVDET_B_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_B_AMT)));
			invDet.setFINVDET_B_DIS_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_B_DIS_AMT)));
			invDet.setFINVDET_BP_DIS_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_BP_DIS_AMT)));
			invDet.setFINVDET_B_SELL_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_B_SELL_PRICE)));
			invDet.setFINVDET_BT_TAX_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_BT_TAX_AMT)));
			invDet.setFINVDET_BT_SELL_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_BT_SELL_PRICE)));
			invDet.setFINVDET_CASE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_CASE)));
			invDet.setFINVDET_CASE_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_CASE_QTY)));
			invDet.setFINVDET_DIS_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_DIS_AMT)));
			invDet.setFINVDET_DIS_PER(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_DIS_PER)));
			invDet.setFINVDET_FREE_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_FREE_QTY)));
			invDet.setFINVDET_ITEM_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_ITEM_CODE)));
			invDet.setFINVDET_P_DIS_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_P_DIS_AMT)));
			invDet.setFINVDET_PRIL_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_PRIL_CODE)));
			invDet.setFINVDET_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_QTY)));
			invDet.setFINVDET_DIS_VAL_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_DIS_VAL_AMT)));
			invDet.setFINVDET_PICE_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_PICE_QTY)));
			invDet.setFINVDET_REA_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_REA_CODE)));
			invDet.setFINVDET_TYPE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TYPE)));
			invDet.setFINVDET_RECORD_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_RECORD_ID)));
			invDet.setFINVDET_REFNO(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_REFNO)));
			invDet.setFINVDET_SELL_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_SELL_PRICE)));
			invDet.setFINVDET_SEQNO(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_SEQNO)));
			invDet.setFINVDET_TAX_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TAX_AMT)));
			invDet.setFINVDET_TAX_COM_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TAX_COM_CODE)));
			invDet.setFINVDET_TIMESTAMP_COLUMN(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TIMESTAMP_COLUMN)));
			invDet.setFINVDET_T_SELL_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_T_SELL_PRICE)));
			invDet.setFINVDET_TXN_DATE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TXN_DATE)));
			invDet.setFINVDET_TXN_TYPE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TXN_TYPE)));
			invDet.setFINVDET_IS_ACTIVE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_IS_ACTIVE)));
			
							
			list.add(invDet);
			
		}
		
		return list;
	}
	
	
	@SuppressWarnings("static-access")
	public int deleteInvDetByID(String id){
		
		int count =0;
		
		if(dB == null){
			open();
		}else if(!dB.isOpen()){
			open();
		}
		Cursor cursor = null;
		try{
			
			cursor = dB.rawQuery("SELECT * FROM " + dbHelper.TABLE_FINVDET +" WHERE "+dbHelper.FINVDET_ID+"='"+id+"'", null);
			count =cursor.getCount();
			if(count>0){
				int success = dB.delete(dbHelper.TABLE_FINVDET, dbHelper.FINVDET_ID+"='"+id+"'", null);
				Log.v("OrdDet Deleted ", success+"");
			}
		}catch (Exception e){

			Log.v(TAG+" Exception", e.toString());
			
		}finally{  
			if (cursor !=null) {
				cursor.close();
			}
			dB.close();
		}
		
		return count;
		
	}
	
	
	public ArrayList<InvDet> getSAForFreeIssueCalc(String refno) {
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		ArrayList<InvDet> list = new ArrayList<InvDet>();
		
		String selectQuery = "select * from "+dbHelper.TABLE_FINVDET +" WHERE "+dbHelper.FINVDET_TXN_TYPE+"='22' AND "+dbHelper.FINVDET_REFNO+"='"+refno+"'";
		
		Cursor cursor = dB.rawQuery(selectQuery, null);
		while(cursor.moveToNext()){
			
			InvDet invDet=new InvDet();
		
			invDet.setFINVDET_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_ID)));
			invDet.setFINVDET_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_AMT)));
			invDet.setFINVDET_BAL_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_BAL_QTY)));
			invDet.setFINVDET_B_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_B_AMT)));
			invDet.setFINVDET_B_DIS_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_B_DIS_AMT)));
			invDet.setFINVDET_BP_DIS_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_BP_DIS_AMT)));
			invDet.setFINVDET_B_SELL_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_B_SELL_PRICE)));
			invDet.setFINVDET_BT_TAX_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_BT_TAX_AMT)));
			invDet.setFINVDET_BT_SELL_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_BT_SELL_PRICE)));
			invDet.setFINVDET_CASE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_CASE)));
			invDet.setFINVDET_CASE_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_CASE_QTY)));
			invDet.setFINVDET_DIS_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_DIS_AMT)));
			invDet.setFINVDET_DIS_PER(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_DIS_PER)));
			invDet.setFINVDET_FREE_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_FREE_QTY)));
			invDet.setFINVDET_ITEM_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_ITEM_CODE)));
			invDet.setFINVDET_P_DIS_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_P_DIS_AMT)));
			invDet.setFINVDET_PRIL_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_PRIL_CODE)));
			invDet.setFINVDET_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_QTY)));
			invDet.setFINVDET_DIS_VAL_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_DIS_VAL_AMT)));
			invDet.setFINVDET_PICE_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_PICE_QTY)));
			invDet.setFINVDET_REA_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_REA_CODE)));
			invDet.setFINVDET_TYPE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TYPE)));
			invDet.setFINVDET_RECORD_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_RECORD_ID)));
			invDet.setFINVDET_REFNO(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_REFNO)));
			invDet.setFINVDET_SELL_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_SELL_PRICE)));
			invDet.setFINVDET_SEQNO(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_SEQNO)));
			invDet.setFINVDET_TAX_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TAX_AMT)));
			invDet.setFINVDET_TAX_COM_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TAX_COM_CODE)));
			invDet.setFINVDET_TIMESTAMP_COLUMN(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TIMESTAMP_COLUMN)));
			invDet.setFINVDET_T_SELL_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_T_SELL_PRICE)));
			invDet.setFINVDET_TXN_DATE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TXN_DATE)));
			invDet.setFINVDET_TXN_TYPE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TXN_TYPE)));
			invDet.setFINVDET_IS_ACTIVE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_IS_ACTIVE)));
			
							
			list.add(invDet);
			
		}
		
		return list;
	}

	public ArrayList<InvDet> getAllActiveVanDet(String refno) {

		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		ArrayList<InvDet> list = new ArrayList<InvDet>();
		//
		//String selectQuery = "select * from "+dbHelper.TABLE_FORDDET +" WHERE "+dbHelper.FORDDET_TXN_TYPE+"!='22' AND "+dbHelper.FORDDET_REFNO+"='"+refno+"'";
		
		String selectQuery = "select * from "+dbHelper.TABLE_FINVDET +" WHERE "+dbHelper.FINVDET_TYPE+" in ('MR','UR','FD') AND "+dbHelper.FINVDET_REFNO+"='"+refno+"'";
		
		
		Cursor cursor = dB.rawQuery(selectQuery, null);
		while(cursor.moveToNext()){
			
			InvDet invDet=new InvDet();
		
			invDet.setFINVDET_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_ID)));
			invDet.setFINVDET_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_AMT)));
			invDet.setFINVDET_BAL_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_BAL_QTY)));
			invDet.setFINVDET_B_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_B_AMT)));
			invDet.setFINVDET_B_DIS_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_B_DIS_AMT)));
			invDet.setFINVDET_BP_DIS_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_BP_DIS_AMT)));
			invDet.setFINVDET_B_SELL_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_B_SELL_PRICE)));
			invDet.setFINVDET_BT_TAX_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_BT_TAX_AMT)));
			invDet.setFINVDET_BT_SELL_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_BT_SELL_PRICE)));
			invDet.setFINVDET_CASE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_CASE)));
			invDet.setFINVDET_CASE_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_CASE_QTY)));
			invDet.setFINVDET_DIS_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_DIS_AMT)));
			invDet.setFINVDET_DIS_PER(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_DIS_PER)));
			invDet.setFINVDET_FREE_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_FREE_QTY)));
			invDet.setFINVDET_ITEM_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_ITEM_CODE)));
			invDet.setFINVDET_P_DIS_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_P_DIS_AMT)));
			invDet.setFINVDET_PRIL_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_PRIL_CODE)));
			invDet.setFINVDET_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_QTY)));
			invDet.setFINVDET_DIS_VAL_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_DIS_VAL_AMT)));
			invDet.setFINVDET_PICE_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_PICE_QTY)));
			invDet.setFINVDET_REA_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_REA_CODE)));
			invDet.setFINVDET_TYPE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TYPE)));
			invDet.setFINVDET_RECORD_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_RECORD_ID)));
			invDet.setFINVDET_REFNO(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_REFNO)));
			invDet.setFINVDET_SELL_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_SELL_PRICE)));
			invDet.setFINVDET_SEQNO(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_SEQNO)));
			invDet.setFINVDET_TAX_AMT(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TAX_AMT)));
			invDet.setFINVDET_TAX_COM_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TAX_COM_CODE)));
			invDet.setFINVDET_TIMESTAMP_COLUMN(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TIMESTAMP_COLUMN)));
			invDet.setFINVDET_T_SELL_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_T_SELL_PRICE)));
			invDet.setFINVDET_TXN_DATE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TXN_DATE)));
			invDet.setFINVDET_TXN_TYPE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_TXN_TYPE)));
			invDet.setFINVDET_IS_ACTIVE(cursor.getString(cursor.getColumnIndex(dbHelper.FINVDET_IS_ACTIVE)));
			
							
			list.add(invDet);
			
		}
		
		return list;
	}

	public String getGrossValue(String refno) {
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}

		//String selectQuery = "SELECT * FROM " + dbHelper.TABLE_FREASON +" WHERE "+dbHelper.FREASON_NAME+"='"+name+"'";
		@SuppressWarnings("static-access")
		String selectQuery = "SELECT SUM("+dbHelper.FINVDET_BAL_QTY+" * "+dbHelper.FINVDET_B_SELL_PRICE+") AS 'Gross Value'  FROM " + dbHelper.TABLE_FINVDET +" WHERE "+dbHelper.FINVDET_TYPE+" ='SA' AND "+dbHelper.FINVDET_REFNO+"='"+refno+"'";
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext()){
			if (cursor.getString(cursor.getColumnIndex("Gross Value"))!=null)
				return cursor.getString(cursor.getColumnIndex("Gross Value"));
			else
				return "0.00";
		}
		
		return "0.00";
	}
	
	public String getTotalReturns(String refno) {
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}

		//String selectQuery = "SELECT * FROM " + dbHelper.TABLE_FREASON +" WHERE "+dbHelper.FREASON_NAME+"='"+name+"'";
		@SuppressWarnings("static-access")
		String selectQuery = "SELECT SUM("+dbHelper.FINVDET_BAL_QTY+" * "+dbHelper.FINVDET_B_SELL_PRICE+") AS 'Total Return'  FROM " + dbHelper.TABLE_FINVDET +" WHERE "+dbHelper.FINVDET_TYPE+" IN('MR','UR') AND "+dbHelper.FINVDET_REFNO+"='"+refno+"'";
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext()){
			if (cursor.getString(cursor.getColumnIndex("Total Return"))!=null)
				return cursor.getString(cursor.getColumnIndex("Total Return"));
			else
				return "0.00";
			
		}
		
		return "0.00";
	}
	
	
	public String getTotalLineDiscount(String refno) {
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		String selectQuery = "SELECT SUM((BalQty * BSellPrice)* disper/100  + DisValAmt) AS 'Total line'  FROM finvdet WHERE types ='SA' AND "+dbHelper.FINVDET_REFNO+"='"+refno+"'";
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext()){
			
			if (cursor.getString(cursor.getColumnIndex("Total line"))!=null)
				return cursor.getString(cursor.getColumnIndex("Total line"));
			else
				return "0.00";
			
			
		}
		
		return "0.00";
	}
	
	@SuppressWarnings("static-access")
	public int InactiveStatusUpdate(String refno) {
		
	int count =0;
		
	if(dB == null){
		open();
	}else if(!dB.isOpen()){
		open();
	}
	Cursor cursor = null;
		
	try{
					
		String selectQuery = "SELECT * FROM " + dbHelper.TABLE_FINVDET 
				+ " WHERE " + dbHelper.FINVDET_REFNO + " = '" +refno + "'";	
			
		cursor = dB.rawQuery(selectQuery, null);
		
		ContentValues values = new ContentValues();
				
		values.put(dbHelper.FINVDET_IS_ACTIVE,"0");
			
		int cn = cursor.getCount();
			
		if(cn>0){
			count = dB.update(dbHelper.TABLE_FINVDET, values,dbHelper.FINVDET_REFNO + " =?",	
							new String[] { String.valueOf(refno)});
		}else{
			count = (int) dB.insert(dbHelper.TABLE_FINVDET, null, values);
		}
				
		
	}catch (Exception e) {
		
		Log.v(TAG+ " Exception", e.toString());
	
	}finally {  
		if (cursor !=null) {
			cursor.close();
			}
		dB.close();
		}
	return count;
				
	}
	
	@SuppressWarnings("static-access")
	public int restData(String refno) {
			
		int count =0;
			
		if(dB == null){
			open();
		}else if(!dB.isOpen()){
			open();
		}
		Cursor cursor = null;
			
		try{
						
			String selectQuery = "SELECT * FROM " + dbHelper.TABLE_FINVDET + " WHERE " + dbHelper.FINVDET_REFNO + " = '" +refno + "'";	
			cursor = dB.rawQuery(selectQuery, null);
			int cn = cursor.getCount();
				
			if(cn>0){
				count = dB.delete(dbHelper.TABLE_FINVDET, dbHelper.FINVDET_REFNO + " ='"+refno+"'", null);
				Log.v("Success Stauts", count+"");
			}
		}catch (Exception e) {
			Log.v(TAG+ " Exception", e.toString());
		}
			finally {  
				if (cursor !=null) {
					cursor.close();
					}
				dB.close();
			}
		return count;
					
	}
	
}
